package apj.demo.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import apj.demo.dao.VendorDao;
import apj.demo.model.Vendor;
import apj.demo.util.JDBCUtil;

public class VendorDaoImpl implements VendorDao {

	private final String LIMIT5 = "select * from vendor limit ?,5";
	private final String FIND_BY_ALL = "select * from vendor";
	private final String FIND_BY_ID = "select * from vendor where vendor_id = ?";
	private final String SAVE = "insert vendor "
			+ "(vendor_id,vendor,vendor_address,vendor_phone,vendor_fax,contact_person) "
			+ "values(?,?,?,?,?,?)";
	private final String UPDATE = "update vendor set "
			+ "vendor=?,vendor_address=?,vendor_phone=?,vendor_fax=?,contact_person=? where vendor_id=?";
	
	/*获取单个页面供货商数据 5条*/
	public List<Vendor> pageData(int index) throws SQLException {
		ArrayList<Vendor> vendors = new ArrayList<Vendor>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			Vendor vendor = null;
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(LIMIT5);
			index = (index*5)-5;
			ps.setInt(1, index);
			rs = ps.executeQuery();
			while(rs.next()){
				vendor = new Vendor();
				vendor.setVendor_id(rs.getInt("vendor_id"));
				vendor.setVendor(rs.getString("vendor"));
				vendor.setVendor_phone(rs.getString("vendor_phone"));
				vendor.setContact_person(rs.getString("contact_person"));
				vendor.setVendor_address(rs.getString("vendor_address"));
				vendor.setVendor_fax(rs.getString("vendor_fax"));
				vendors.add(vendor);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.close(rs);
			JDBCUtil.close(ps);
			JDBCUtil.close(conn);
		}
		return vendors;
	}
	
	/*获取全部供货商数据*/
	public List<Vendor> findByAll() throws SQLException {
		ArrayList<Vendor> vendors = new ArrayList<Vendor>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			Vendor vendor = null;
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(FIND_BY_ALL);
			rs = ps.executeQuery();
			while(rs.next()){
				vendor = new Vendor();
				vendor.setVendor_id(rs.getInt("vendor_id"));
				vendor.setVendor(rs.getString("vendor"));
				vendor.setVendor_phone(rs.getString("vendor_phone"));
				vendor.setContact_person(rs.getString("contact_person"));
				vendor.setVendor_address(rs.getString("vendor_address"));
				vendor.setVendor_fax(rs.getString("vendor_fax"));
				vendors.add(vendor);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.close(rs);
			JDBCUtil.close(ps);
			JDBCUtil.close(conn);
		}
		return vendors;
	}

	/*根据id获取供货商的信息*/
	public Vendor findById(int id) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Vendor vendor = null;
		try{
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(FIND_BY_ID);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if(rs.next()){
				vendor = new Vendor();
				vendor.setVendor_id(rs.getInt("vendor_id"));
				vendor.setVendor(rs.getString("vendor"));
				vendor.setVendor_phone(rs.getString("vendor_phone"));
				vendor.setContact_person(rs.getString("contact_person"));
				vendor.setVendor_address(rs.getString("vendor_address"));
				vendor.setVendor_fax(rs.getString("vendor_fax"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.close(rs);
			JDBCUtil.close(ps);
			JDBCUtil.close(conn);
		}
		return vendor;
	}

	/*保存供货商信息*/
	public void save(Vendor vendor) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		try{
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(SAVE);
				ps.setInt(1, vendor.getVendor_id());
				ps.setString(2,vendor.getVendor());
				ps.setString(3,vendor.getVendor_address());
				ps.setString(4,vendor.getVendor_phone());
				ps.setString(5,vendor.getVendor_fax());
				ps.setString(6,vendor.getContact_person());
				ps.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.close(ps);
			JDBCUtil.close(conn);
		}
	}

	/*更新供货商信息*/
	public void update(Vendor vendor) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		try{
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(UPDATE);
				ps.setString(1,vendor.getVendor());
				ps.setString(2,vendor.getVendor_address());
				ps.setString(3,vendor.getVendor_phone());
				ps.setString(4,vendor.getVendor_fax());
				ps.setString(5,vendor.getContact_person());
				ps.setInt(6, vendor.getVendor_id());
				ps.executeUpdate();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.close(ps);
			JDBCUtil.close(conn);
		}
	}

}
